/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.absisgroup.nsc.home;

import com.absisgroup.nsc.utilities.FileObject;
import com.absisgroup.nsc.utilities.GenericServlet;
import com.absisgroup.nsc.utilities.Upload;
import java.io.IOException;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Hashtable;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.json.JSONArray;
import org.json.JSONObject;
import org.json.XML;

/**
 *
 * @author Hue
 */
public class LoadQuery extends GenericServlet {

    @Override
    public int process(final Connection con, final HttpServletRequest request, final HttpServletResponse response) throws Exception {
        final Hashtable params = Upload.getMultipartParameters(request);

        String str = null;
        final String fileType = (String) params.get("fileType");
        if("FILE_SYSTEM".equals(fileType)) {
            final FileObject fo = (FileObject) params.get("fileSystem");
            if(fo != null && fo.getData() != null) {
                str = toJSONObject(con, fo.getData());
            }
        }

        if(str == null || str.isEmpty()) {
            str = getError(fileType);
        }
        writeToResponse(response, str);
            
        return DONT_RUN_JSP;
    }
    
    private String toJSONObject(final Connection con, final byte[] data) throws Exception {
        final JSONObject jsObj = new JSONObject();
        jsObj.put("success", true);
        jsObj.put("msg", "");
        final JSONObject jsData = XML.toJSONObject(new String(data));
        jsObj.put("data", jsData);

        BigDecimal nquePk = null;
        if(jsData.has("query")) {
            final JSONObject jsQuery = jsData.getJSONObject("query");
            if("1".equals(getString(jsQuery, "isautosaved"))) {
                nquePk = saveQuery(con, jsQuery);
                con.commit();
            }
            if(nquePk != null) {
                jsQuery.put("nquePk", nquePk.toString());
            }
        }

        return jsObj.toString();
    }
    
    private String getError(final String fileType) throws Exception {
        final JSONObject jsObj = new JSONObject();
        jsObj.put("success", false);
        jsObj.put("msg", "Unknow error [fileType=" + fileType + "]!");

        return jsObj.toString();
    }
    
    private BigDecimal saveQuery(final Connection con, final JSONObject jsQuery) throws Exception {
        final String category = jsQuery.getJSONObject("category").getString("content");
        String strQuery = "select ncat_pk from nsc_category where ncat_code = ?";
        PreparedStatement pStmt = con.prepareStatement(strQuery);
        pStmt.setString(1, category);
        ResultSet rs = pStmt.executeQuery();
        BigDecimal ncatPk = null;
        if(rs.next()) {
            ncatPk = rs.getBigDecimal("ncat_pk");
        }
        pStmt.close();
        
        //If the category doesn't exists, create it [
        if(ncatPk == null) {
            ncatPk = createCategory(con, category);
        }
        //]
        final String code = jsQuery.getString("code");
        strQuery = "select nque_pk from nsc_query where nque_code = ?";
        pStmt = con.prepareStatement(strQuery);
        pStmt.setString(1, code);
        rs = pStmt.executeQuery();
        BigDecimal nquePk = null;
        if(rs.next()) {
            nquePk = rs.getBigDecimal("nque_pk");
        }
        pStmt.close();
        
        if(nquePk == null) {
            nquePk = createQuery(con, ncatPk, jsQuery);
        }
        else {
            updateQuery(con, nquePk, jsQuery);
        }
        createQueryParameters(con, nquePk, jsQuery);
        
        return nquePk;
    }
    
    private BigDecimal createCategory(final Connection con, final String category) throws Exception {
        String strQuery = "select sq_ncat_pk.nextval from dual";
        PreparedStatement pStmt = con.prepareStatement(strQuery);
        ResultSet rs = pStmt.executeQuery();
        BigDecimal ncatPk = null;
        if(rs.next()) {
            ncatPk = rs.getBigDecimal(1);
        }
        pStmt.close();

        strQuery = "insert into nsc_category(ncat_pk, ncat_code, ncat_name) values (?, ?, ?)";
        pStmt = con.prepareStatement(strQuery);
        pStmt.setBigDecimal(1, ncatPk);
        pStmt.setString(2, category);
        pStmt.setString(3, category);
        pStmt.executeUpdate();
        pStmt.close();
        
        return ncatPk;
    }
    
    private BigDecimal createQuery(final Connection con, final BigDecimal ncatPk, final JSONObject jsQuery) throws Exception {
        String strQuery = "select sq_nque_pk.nextval from dual";
        PreparedStatement pStmt = con.prepareStatement(strQuery);
        ResultSet rs = pStmt.executeQuery();
        BigDecimal nquePk = null;
        if(rs.next()) {
            nquePk = rs.getBigDecimal(1);
        }
        pStmt.close();

        strQuery = "insert into nsc_query(nque_pk, nque_ncat_fk, nque_code, nque_name, nque_creator, nque_description, nque_isreadonly, nque_isadmin, nque_statement) " + 
                   "values (?, ?, ?, ?, ?, ?, ?, ?, ?)";
        pStmt = con.prepareStatement(strQuery);
        int iIndex = 1;
        pStmt.setBigDecimal(iIndex++, nquePk);
        pStmt.setBigDecimal(iIndex++, ncatPk);
        pStmt.setString(iIndex++, getString(jsQuery, "code"));
        pStmt.setString(iIndex++, getString(jsQuery, "name"));
        pStmt.setString(iIndex++, getString(jsQuery, "creator"));
        pStmt.setString(iIndex++, getString(jsQuery, "comment"));
        pStmt.setString(iIndex++, getString(jsQuery, "isreadonly"));
        pStmt.setString(iIndex++, getString(jsQuery, "isadmin"));
        pStmt.setString(iIndex++, getString(jsQuery, "statement"));
        pStmt.executeUpdate();
        pStmt.close();
        
        return nquePk;
    }
    
    private void updateQuery(final Connection con, final BigDecimal nquePk, final JSONObject jsQuery) throws Exception {
        final String strQuery = "update nsc_query set nque_name = ?, nque_creator = ?, nque_description = ?, " + 
                                "       nque_isreadonly = ?, nque_isadmin = ?, nque_statement = ?, NQUE_LASTUPDATE = sysdate " + 
                                "where nque_pk = ?";
        final PreparedStatement pStmt = con.prepareStatement(strQuery);
        int iIndex = 1;
        pStmt.setString(iIndex++, getString(jsQuery, "name"));
        pStmt.setString(iIndex++, getString(jsQuery, "creator"));
        pStmt.setString(iIndex++, getString(jsQuery, "comment"));
        pStmt.setString(iIndex++, getString(jsQuery, "isreadonly"));
        pStmt.setString(iIndex++, getString(jsQuery, "isadmin"));
        pStmt.setString(iIndex++, getString(jsQuery, "statement"));
        pStmt.setBigDecimal(iIndex++, nquePk);
        pStmt.executeUpdate();
        pStmt.close();
    }
    
    private void createQueryParameters(final Connection con, final BigDecimal nquePk, final JSONObject jsQuery) throws Exception {
        final String strQuery = "delete from nsc_query_parameter where nquep_nque_fk = ?";
        final PreparedStatement pStmt = con.prepareStatement(strQuery);
        pStmt.setBigDecimal(1, nquePk);
        pStmt.executeUpdate();
        pStmt.close();
        
        if(jsQuery.has("parameters")) {
            final JSONObject jsParams = jsQuery.getJSONObject("parameters");
            if(jsParams.has("parameter")) {
                final JSONArray arr = jsParams.getJSONArray("parameter");
                final int iLength = arr.length();
                for(int i=0; i<iLength; i++) {
                    createParameter(con, nquePk, arr.getJSONObject(i));
                }
            }
        }
    }
    
    private void createParameter(final Connection con, final BigDecimal nquePk, final JSONObject jsObj) throws Exception {
        final String strQuery = "insert into nsc_query_parameter(NQUEP_PK, NQUEP_NQUE_FK, NQUEP_CODE, NQUEP_NAME, NQUEP_SORTORDER, NQUEP_DATATYPE) " + 
                                "select SQ_NQUEP_PK.nextval, ?, ?, ?, ?, decode(?,'STRING',1,'NUMBER',2,'DECIMAL',3,'DATE',4,'DATETIME',5,0) NQUEP_DATATYPE " + 
                                "from dual";
        final PreparedStatement pStmt = con.prepareStatement(strQuery);
        int iIndex = 1;
        pStmt.setBigDecimal(iIndex++, nquePk);
        pStmt.setString(iIndex++, getString(jsObj, "code"));
        pStmt.setString(iIndex++, getString(jsObj, "name"));
        pStmt.setString(iIndex++, getString(jsObj, "sortorder"));
        pStmt.setString(iIndex++, getString(jsObj, "datatype"));
        pStmt.executeUpdate();
        pStmt.close();
    }
}
